import sqlite3
import threading
from queue import Queue


class SQLiteConnectionPool:
    """SQLite 数据库连接池操作类"""
    def __init__(self, db_name, max_connections=10):
        """初始化，连接到指定的数据库连接池"""
        self.pool = Queue(maxsize=max_connections)
        self.lock = threading.Lock()
        for _ in range(max_connections):
            self.pool.put(sqlite3.connect(db_name))

    def release_connection(self, conn):
        """关闭数据库连接"""
        with self.lock:
            if not self.pool.full():
                self.pool.put(conn)
            else:
                conn.close()

    def get_connection(self):
        """获取数据库链接"""
        with self.lock:
            if not self.pool.empty():
                return self.pool.get()
            else:
                raise Exception("No available connections in the pool")


class Sqlite3Database(object):
    """SQLite 数据库连接操作类"""

    def __init__(self, db_name):
        """初始化，连接到指定的数据库"""
        self.pool = SQLiteConnectionPool(db_name)
        self.conn = self.pool.get_connection()
        self.cursor = self.conn.cursor()

    def __del__(self):
        """关闭数据库连接"""
        if self.conn:
            self.pool.release_connection(self.conn)

    def create_table(self, table_name, columns: str):
        """创建表格"""
        sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})"
        self.cursor.execute(sql)
        self.conn.commit()

    def drop_table(self, table_name):
        """删除表格"""
        sql = f"DROP TABLE IF EXISTS {table_name}"
        self.cursor.execute(sql)
        self.conn.commit()

    def insert_data(self, table_name, data: dict):
        """插入数据"""
        columns = ', '.join(data.keys())
        placeholders = ':' + ', :'.join(data.keys())
        sql = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
        self.cursor.execute(sql, data)
        self.conn.commit()

    def batch_insert_data_old(self, table_name, data: list):
        """批量 插入数据  <拼接方式>"""
        columns = ', '.join(data[0].keys())
        values = ''
        for row in data:
            placeholders = row.values()
            values = str(tuple(placeholders)) + ',' + values
        sql = f"INSERT OR REPLACE INTO {table_name} ({columns}) VALUES {values[:-1]}"
        self.cursor.execute(sql)
        self.conn.commit()

    def batch_insert_data(self, table_name, data: list):
        """ 批量 插入数据  <占位方式> """
        columns = ', '.join(data[0].keys())
        values = []
        for row in data:
            placeholders = row.values()
            values.append(tuple(placeholders))
        sql = f"INSERT OR REPLACE INTO {table_name} ({columns}) VALUES ({', '.join(['?'] * len(data[0].keys()))})"
        self.cursor.executemany(sql, values)
        self.conn.commit()

    def batch_update_data(self, table_name, updates: dict, condition_column, update_column):
        """
        批量更新数据
        :param table_name: 表名
        :param update_column: 对应的设值 列
        :param updates: 数据格式如下： 假设我们有一个 id 到 (name, age) 的映射，我们要根据这个映射来更新 users 表
                 updates = {
                            “Horatio1990005@fastmail.com”: '194.113.238.136',
                            "Elias1990004@fastmail.com":  '95.164.150.18',
                            "Victor1990003@fastmail.com": '50.114.106.62'
                        }
        :param condition_column: 用于筛选数据的条件列名
            sql :
                    UPDATE rapid_bin_info
                    SET
                        bind_proxy_ip = CASE
                        WHEN account = 'Horatio1990005@fastmail.com' THEN '194.113.238.136'
                        WHEN account = 'Victor1990003@fastmail.com' THEN '50.114.106.62'
                        ELSE null END
                    WHERE
                        account IN ('Horatio1990005@fastmail.com','Victor1990003@fastmail.com')
        :return:
        """
        updates_list = list(updates.values())
        accounts = list(updates.keys())
        # 准备SQL语句和参数列表
        update_sql = f"UPDATE {table_name} SET {update_column}= CASE"
        params = []
        # 构建SET子句和参数列表
        for i, update in enumerate(updates_list):
            update_sql += f" WHEN {condition_column} = ? THEN ? "
            params.append(str(accounts[i]))  # 添加的账号值
            params.append(update)  # 添加的 ip 值
        # 添加WHERE子句来限制更新的行
        update_sql += f"ELSE null END WHERE {condition_column} IN (" + ", ".join(["?"] * len(accounts)) + ")"
        params.extend(accounts)  # 添加所有ID到参数列表
        # 执行更新语句
        self.cursor.execute(update_sql, params)
        self.conn.commit()

    def batch_insert_data_by_tuple(self, table_name, data: list, headers: tuple):
        """批量 插入数据"""
        values = ''
        for row in data:
            values = str(row) + ',' + values
        sql = f"INSERT OR REPLACE INTO {table_name} {headers} VALUES {values[:-1]}"
        self.cursor.execute(sql)
        self.conn.commit()

    def insert_data_tuple(self, table_name, data: tuple, headers: tuple):
        """通过元组插入数据"""
        sql = f"INSERT OR REPLACE INTO {table_name} {headers} VALUES {data}"
        self.cursor.execute(sql)
        self.conn.commit()

    def update_data(self, table_name, data: dict, condition):
        """更新数据"""
        sql = ''
        try:
            set_clause = ', '.join(f"{key} = :{key}" for key in data.keys())
            sql = f"UPDATE {table_name} SET {set_clause} WHERE {condition}"
            self.cursor.execute(sql, data)
            self.conn.commit()
        except Exception as e:
            print(sql)
            raise Exception(e)

    def delete_data(self, table_name, condition):
        """删除数据"""
        sql = f"DELETE FROM {table_name} WHERE {condition}"
        self.cursor.execute(sql)
        self.conn.commit()

    def select_data(self, table_name, columns=None, condition=None):
        """查询数据"""
        if columns is None:
            columns = '*'
        if condition is None:
            condition = ''
        else:
            condition = f"WHERE {condition}"
        sql = f"SELECT {columns} FROM {table_name} {condition}"
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def select_data_by_limit(self, table_name, columns=None, condition=None, limit=None):
        """查询数据"""
        if columns is None:
            columns = '*'
        if condition is None:
            condition = ''
        else:
            condition = f"WHERE {condition}"
        if limit is None:
            limit = ''
        else:
            limit = limit
        sql = f"SELECT {columns} FROM {table_name} {condition} {limit}"
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def select_exe_sql(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()


if __name__ == '__main__':
    column = "id int primary key , user_name VARCHAR(200) NOT NULL, password" \
             " VARCHAR(200) not null, auto int not null, save int not null"
    table_names = "user_info"
    db_names = "crawler.db"
    datas = {'user_name': 'Master', 'password': '12ss3456', 'auto': 0, 'save': 1}
    sqlite3s = Sqlite3Database(db_names)
    sqlite3s.create_table(table_names, column)
    sqlite3s.insert_data(table_names, datas)
    datas = {'id': 11, 'user_name': 'Master', 'password': '12356111111111', 'auto': 2, 'save': 1}
    sqlite3s.update_data(table_names, datas, "user_name =:user_name")
